using System.Collections.Generic;
using Microsoft.Data.Sqlite;
using 第六次作业.entity;

namespace 第六次作业.utils.crud;

public class ClassRepository
{
    public void Create(Class @class)
    {
        using (SqliteConnection connection = DatabaseUtil.GetConnection())
        {
            connection.Open();
            List<Class> classes = new List<Class>();
            classes = GetAll(@class.SchoolId);
            int num = classes.Count;
            SqliteCommand command = connection.CreateCommand();
            command.CommandText = "INSERT INTO Class (Id, Name, SchoolId) VALUES (@Id, @Name, @SchoolId)";
            command.Parameters.AddWithValue("@Name", @class.Name);
            command.Parameters.AddWithValue("@SchoolId", @class.SchoolId);
            command.Parameters.AddWithValue("@Id", num + 1);
            command.ExecuteNonQuery();
        }
    }

    public void Delete(int id)
    {
        using (SqliteConnection connection = DatabaseUtil.GetConnection())
        {
            connection.Open();
            SqliteCommand command = connection.CreateCommand();
            command.CommandText = "DELETE FROM Class WHERE Id = @Id";
            command.Parameters.AddWithValue("@Id", id);
            command.ExecuteNonQuery();
        }
    }

    public void Update(Class @class)
    {
        using (SqliteConnection connection = DatabaseUtil.GetConnection())
        {
            connection.Open();
            SqliteCommand command = connection.CreateCommand();
            command.CommandText = "UPDATE Class SET Name = @Name, SchoolId = @SchoolId WHERE Id = @Id";
            command.Parameters.AddWithValue("@Name", @class.Name);
            command.Parameters.AddWithValue("@SchoolId", @class.SchoolId);
            command.Parameters.AddWithValue("@Id", @class.Id);
            command.ExecuteNonQuery();
        }
    }

    public List<Class> GetAll(int schoolId)
    {
        List<Class> classes = new List<Class>();
        using (SqliteConnection connection = DatabaseUtil.GetConnection())
        {
            connection.Open();
            SqliteCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM Class WHERE SchoolId = @SchoolId";
            command.Parameters.AddWithValue("@SchoolId", schoolId);
            SqliteDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Class cls = new Class
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    SchoolId = reader.GetInt32(2)
                };
                classes.Add(cls);
            }
        }
        return classes;
    }
    
    public Class GetById(int id)
    {
        Class cls = null;
        using (SqliteConnection connection = DatabaseUtil.GetConnection())
        {
            connection.Open();
            SqliteCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM Class WHERE Id = @Id";
            command.Parameters.AddWithValue("@Id", id);
            SqliteDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                cls = new Class
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    SchoolId = reader.GetInt32(2)
                };
            }
        }
        return cls;
    }
    
    public void DeleteBySchoolId(int schoolId)
    {
        using (SqliteConnection connection = DatabaseUtil.GetConnection())
        {
            connection.Open();
            SqliteCommand command = connection.CreateCommand();
            command.CommandText = "DELETE FROM Class WHERE SchoolId = @SchoolId";
            command.Parameters.AddWithValue("@SchoolId", schoolId);
            command.ExecuteNonQuery();
        }
    }
}